package org.sean.contactdemo.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.sean.contactdemo.domain.Group;

public class GroupDAO extends AbstractDAO {

	public GroupDAO(){
		super();
	}
	
	public GroupDAO(Connection connection){
		super(connection);
	}
	
	public Group findGroupByID(int groupId){
		StringBuffer sql = new StringBuffer();
		sql.append("select * from cd_group where group_id =").append(groupId);
		Group group = null;
		ResultSet rs = getResultSetAfterQuery(sql.toString());
		try {
			rs.last();
			if (rs != null && rs.getRow() == 1){
				group = new Group();
				group.setGroupId(rs.getInt("group_id"));
				group.setUserId(rs.getInt("user_id"));
				group.setGroupName(rs.getString("group_name"));
				group.setDefault(rs.getString("is_default").equals("Y") ? true : false);
			}
		} catch (SQLException e) {
			error(e);
		} finally {
			releaseDBResource(rs);
		}
		return group;
	}
	
	public List<Group> findGroupByUserId(int userId){
		StringBuffer sql = new StringBuffer();
		sql.append("select group_id from cd_group where user_id =").append(userId);
		return getGroupResult(sql.toString());
	}
	
	public List<Group> getGroupResult(String sql){
		List<Group> groups = new ArrayList<Group>();
		ResultSet rs = getResultSetAfterQuery(sql);
		try {
			if (rs != null){
				while (rs.next()){
					int groupId = rs.getInt("group_id");
					Group group = findGroupByID(groupId);
					if (group != null){
						groups.add(group);
					}
				}
			}
		} catch (SQLException e){
			error(e);
		} finally {
			releaseDBResource(rs);
		}
		return groups;
	}
	
	public Group addGroup(Group newGroup){
		Group group = null;
		StringBuffer sql = new StringBuffer();
		sql.append("insert into cd_group(user_id, group_name, is_default) values (");
		sql.append(newGroup.getUserId()).append(",");
		sql.append("'" + newGroup.getGroupName() + "'").append(",");
		sql.append("'").append(newGroup.isDefault()? "Y":"N").append("'");
		sql.append(")");
		boolean bSuccess = excute(sql.toString());
		//Find the group by the largest group_id & user_id
		if (bSuccess){
			sql = new StringBuffer();
			sql.append("select max(group_id) as group_id from cd_group where user_id =").append(newGroup.getUserId());
			sql.append(" and ");
			sql.append(" group_name = '").append(newGroup.getGroupName()).append("'");
			sql.append(" and ");
			sql.append("is_default = '").append(newGroup.isDefault()? "Y":"N").append("'");
			List<Group> groups = getGroupResult(sql.toString());
			if (groups != null && groups.size() > 0){
				group = groups.get(0);
			}
		}
		return group;
	}
}
